-- ===============================================
-- 
-- Configure Data Access Quick Starts Transfer Database 
-- Process : Update store procedure
-- Create by : utehn.s@gmail.com
-- Date : 11/09/2008 : 5:00PM
-- 
-- ===============================================

USE [Transfer]
GO

if exists (select * from sysobjects where id = object_id(N'[sp_CheckTransfer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [sp_CheckTransfer]
GO 

CREATE PROC sp_CheckTransfer
(	
	@Id       nvarchar(9) = ''	
)
AS 
BEGIN 	
	-- Create temp table
	CREATE TABLE #tblTmp (
	     Id nvarchar(9),
		 Detail nvarchar(500), 
		 OldDetail nvarchar(500),
		 CourseNo nvarchar(10))

	-- Declare the variables to store the values returned by FETCH.
	DECLARE @localId nvarchar(9), @localDetail nvarchar(500), @localCourseNo nvarchar(10)

	DECLARE pre_prerequisite_cursor CURSOR FOR
	SELECT     dbo.Transcourseno.id, dbo.pre_prerequisite.DETAIL, dbo.Transcourseno.CourseNo
	FROM       dbo.Transcourseno INNER JOIN
                      dbo.pre_prerequisite ON dbo.Transcourseno.courseno = dbo.pre_prerequisite.COURSENO INNER JOIN
                      dbo.Transmaster ON dbo.Transcourseno.id = dbo.Transmaster.id
	WHERE Transcourseno.Id = @Id

	OPEN pre_prerequisite_cursor

	-- Perform the first fetch and store the values in variables.
	-- Note: The variables are in the same order as the columns
	-- in the SELECT statement. 

	FETCH NEXT FROM pre_prerequisite_cursor
	INTO @localId, @localDetail, @localCourseNo

	-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
	WHILE @@FETCH_STATUS = 0
	BEGIN

	   -- Concatenate and display the current values in the variables.
	   --PRINT 'Testing: ' + @localId + ' ' +  @localDetail
			
			SET TEXTSIZE 0
			SET NOCOUNT ON
			-- Create the variables for the current character string position 
			-- and for the character string.
			DECLARE @position int, @string char(100), @newDetail nvarchar(500), @x int
			-- Initialize the variables.
			SET @x = 1
			SET @position = 1
			SET @string = @localDetail
			SET @newDetail = ''

			WHILE @position <= DATALENGTH(@localDetail)
			   BEGIN		

				-- ==================
				-- only interger
				-- ==================

				IF (ASCII(SUBSTRING(@string, @position, 1)) >= 48) AND (ASCII(SUBSTRING(@string, @position, 1)) <= 57)
					BEGIN																	
						IF @x = 6
							BEGIN							
								SET @newDetail = @newDetail + ''+ CHAR(ASCII(SUBSTRING(@string, @position, 1))) + ''''														
								-- reset
								SET @x = 0 
							END
						ELSE
							IF @x = 1 
								BEGIN
									SET @newDetail = @newDetail + 'courseno = '''+ CHAR(ASCII(SUBSTRING(@string, @position, 1))) + ''
								END
							ELSE
								SET @newDetail = @newDetail + ''+ CHAR(ASCII(SUBSTRING(@string, @position, 1))) + ''
																		
						SET @x = @x + 1														
					END
				ELSE

					-- ==================
					-- Special ascii
					-- ==================

					-- Check '[' 	
					IF (ASCII(SUBSTRING(@string, @position, 1)) <> 91) 
						BEGIN 						
							SET @newDetail = @newDetail + CHAR(ASCII(SUBSTRING(@string, @position, 1)))				
						END			
					ELSE 
						BEGIN
							-- Check spec bar
							IF (ASCII(SUBSTRING(@string, @position, 1)) <> 91) AND (ASCII(SUBSTRING(@string, @position, 1)) <> 32)				
								SET @newDetail = @newDetail + CHAR(ASCII(SUBSTRING(@string, @position, 1)))					
							ELSE 				
								BREAK 	
						END									
	
					-- Remove "concurrent to"
					SET @newDetail = REPLACE(@newDetail,'concurrent to ','')
																																																							
				SET @position = @position + 1									
			   END
			SET NOCOUNT OFF
				
			IF (LEN(@newDetail) > 0)
				BEGIN
					SET @newDetail = '(id = ''' + @Id + ''') and (' +  RTRIM(@newDetail) +')'			
					-- Insert value into table
					INSERT INTO #tblTmp VALUES (@localId, @newDetail, '('+RTRIM(REPLACE(@localDetail,'or ',','))+')', @localCourseNo)			
				END
			ELSE
				BEGIN
					SET @newDetail = '(id = ''' + @Id + ''')'			
					-- Insert value into table
					INSERT INTO #tblTmp VALUES (@localId, @newDetail, ''+RTRIM(REPLACE(@localDetail,'or ',','))+'', @localCourseNo)
				END
						
	   -- This is executed as long as the previous fetch succeeds.
	   FETCH NEXT FROM pre_prerequisite_cursor
	   INTO @localId, @localDetail, @localCourseNo
	END

	CLOSE pre_prerequisite_cursor
	DEALLOCATE pre_prerequisite_cursor

	SELECT * FROM #tblTmp WHERE LEN(Detail) > 0
	DROP TABLE #tblTmp
END
GO


-- Example : 

------USE Transfer
------GO
----
----
----DECLARE @CourseNo nvarchar(20), @Id nvarchar(9)
----SET @CourseNo = '001109'
----SET @Id = '510110127'
----
------ step 1
----select @CourseNo = courseNo  from  transcourseno where  id = @Id
----
------ step 2 
------EXEC sp_CheckTransfer @CourseNo
----
----select * from pre_prerequisite where courseno = @CourseNo
----
------ step 3 
----select * from transcourseno inner join 
----	pre_prerequisite on transcourseno.courseno = pre_prerequisite.courseno
----where id = '510110127'
--
----SELECT SUBSTRING(id,3,2) AS FileLoacation, courseno FROM Transcourseno WHERE id = '510110127'
--GO 
--
--
--
---- 1 : 
----SELECT        no, id, courseno, insertDate, title, credit, credit_unit, grade, Idpre, Namepre
----FROM            Transcourseno
----WHERE        (id = '510110127')
--
--
---- 2 : 
----select * from pre_prerequisite where courseNo IN ('001103','001203','001205')
--
----GO 
----
sp_CheckTransfer '510110318'
----
----GO 
--
--
--select * from Transcourseno where id = 510110127

--select * from acad01 where
----courseno = '050102' or courseno = '001192' or courseno = '001104' or courseno = '001107'   
--courseno = '001203'                                       
                                 
--select Id, * from acad01 order by id DESC
--where 
--(id = '510110007') and (courseno = '050102' or courseno = '001192' or courseno = '001104' or courseno = '001107')

--id = '510110007' and 
--courseno = '050102' or courseno = '001192' or courseno = '001104' or courseno = '001107' 

--courseno = '001203' 
